# -*- coding: utf-8 -*-

import sqlite3
from CSVTools import CSVData
from unicodedata import normalize

class RebidesDB:    

    def __init__( self ):
        pass
    
    ##http://www.python.org.br/wiki/RemovedorDeAcentos
    def normalizeString( self, text, codif = 'utf-8' ):
        return normalize( 'NFKD', text.decode( codif ) ).encode( 'ASCII','ignore' )
    
    def addIntoDatabase( self, csvName, databaseName, databaseTable ):
        
        data = CSVData().readCSV( csvName )

        for i in xrange( 0, len( data ) ):
            for j in xrange(0, len( data[ i ] ) ):
                data[ i ][ j ] = self.normalizeString( data[ i ][ j ] )
                pass
            pass

        conn = sqlite3.connect( databaseName )
        c = conn.cursor()

        c.execute( '''create table if not exists {0} ("{1}" text, "{2}" text, "{3}" text, "{4}" text, "{5}" text, "{6}" text, "{7}" text, "{8}" text, "{9}" text)'''
                  .format(databaseTable, data[ 0 ][ 0 ], data[ 0 ][ 1 ], data[ 0 ][ 2 ], data[ 0 ][ 3 ], data[ 0 ][ 4 ], data[ 0 ][ 5 ], data[ 0 ][ 6 ], data[ 0 ][ 7 ], data[ 0 ][ 8 ] ) )

        conn.close()

        conn = sqlite3.connect( databaseName )
        c = conn.cursor()

        for i in xrange( 1, len ( data ) ):
            c.execute( '''insert into {0} ( "Codigo do Docente", "Nome", "Ultimo Grau", "Curso", "Categoria", "Regime de Tempo", "Ano Lectivo", "Tipo de Estabelecimento", "Nome do Estabelecimento" )
                        values ("{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}", "{9}")'''
                      .format(databaseTable, data[ i ][ 0 ].replace( "\"", "'" ), data[ i ][ 1 ].replace( "\"", "'" ), data[ i ][ 2 ].replace( "\"", "'" ), data[ i ][ 3 ].replace( "\"", "'" ),
                      data[ i ][ 4 ].replace( "\"", "'" ), data[ i ][ 5 ].replace( "\"", "'" ), data[ i ][ 6 ].replace( "\"", "'" ), data[ i ][ 7 ].replace( "\"", "'" ), data[ i ][ 8 ].replace( "\"", "'" ) ) )
            pass
        conn.commit()
        conn.close()
        pass

    def loadDatabaseRebides( self, databaseName ):
        conn = sqlite3.connect( databaseName )
        c = conn.cursor()

        data = c.execute( '''select * from teachers''').fetchall()

        names = []
        c.execute( '''select distinct "Nome do Estabelecimento" from teachers''' )
        
        for row in c:
            names.append( row[ 0 ] )
            pass


        acad_years = []
        c.execute( '''select distinct "Ano Lectivo" from teachers''' )

        for row in c:
            acad_years.append( row[ 0 ] )
            pass

        
        categorys = []
        c.execute( '''select distinct "Categoria" from teachers''' )

        for row in c:
            categorys.append( row[ 0 ] )
            pass


        grades = []
        c.execute( '''select distinct "Ultimo Grau" from teachers''' )

        for row in c:
            grades.append( row[ 0 ] )
            pass

        conn.close()
        return [ data, names, acad_years, categorys, grades ]
        pass